package com.atguigu.tingshu.album.mapper;

import com.atguigu.tingshu.model.album.BaseCategory3;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface BaseCategory3Mapper extends BaseMapper<BaseCategory3> {

    /**
     * 首页：根据一级分类id获取三级分类top7 SQL
     * @param category1Id
     * @return
     */
    @Select("SELECT\n" +
            "\tc3.id,\n" +
            "\tc3.name,\n" +
            "\tc3.category2_id,\n" +
            "\tc3.order_num,\n" +
            "\tc3.is_top \n" +
            "FROM\n" +
            "\tbase_category2 c2\n" +
            "\tINNER JOIN base_category3 c3 ON c2.id = c3.category2_id \n" +
            "WHERE\n" +
            "\tc2.category1_id = #{category1Id} \n" +
            "\tAND c2.is_deleted = 0 \n" +
            "\tAND c3.is_deleted = 0 \n" +
            "ORDER BY\n" +
            "\tc3.is_top \n" +
            "\tLIMIT 0,\n" +
            "\t7")
    public List<BaseCategory3> selectTopBaseCategory3(@Param("category1Id") Long category1Id);

}
